#   分别显示满足以下条件的数据
# - 年龄(age) >= 30 的所有数据
# - 列举出所有的国家(Native country),及其数量
# - 年龄(age) >= 32,且显示的所有男性的年龄(age)大于女性的平均年龄(age)
# - 只显示教育长度(education)数量最多,年龄(age)>=32 的数据
import pandas as pd
import pymysql


class MySqlHelper:
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        self.cursor = self.connection.cursor()

    def close(self):
        self.cursor.close()
        self.connection.close()

    def select(self, sql):
        try:
            self.connect()
            self.cursor.execute(sql)
            rows = self.cursor.fetchall()
            self.close()
            return rows
        except Exception as e:
            print(e)


def create_table(data_path):
    data = pd.read_csv(data_path)
    helper = MySqlHelper('localhost', 'root', 'ak47qbz95', 'hqyj')
    helper.connect()

    helper.cursor.execute(
        '''
        create table if not exists pandas_data(
        id int primary key auto_increment,
        name varchar(20),
        age int,
        nationality varchar(20),
        education varchar(20),
        gender varchar(10)
        );
        '''
    )
    sql = '''
    insert into pandas_data (name,age,nationality,education,gender)
    values (%s, %s, %s, %s, %s);
    '''
    pandas_data = data.to_dict('records')
    params = [
        (i['name'], i['age'], i['nationality'], i['education'], i['gender'])
        for i in pandas_data
    ]
    helper.cursor.executemany(sql, params)
    # for _, i in data.iterrows():
    #     params = (i['name'], i['age'], i['nationality'], i['education'], i['gender'])
    #     cursor.execute(sql, params)
    helper.connection.commit()
    helper.close()

# 年龄(age) >= 30 的所有数据
def select_1():
    helper = MySqlHelper('localhost', 'root', 'ak47qbz95', 'hqyj')
    sql = '''
    select * from pandas_data where age >= 30;
    '''
    rows = helper.select(sql)
    print(rows)


# - 列举出所有的国家(Native country),及其数量
def select_2():
    helper = MySqlHelper('localhost', 'root', 'ak47qbz95', 'hqyj')
    sql = '''
    select nationality,count(nationality) as count from pandas_data
    group by nationality;
    '''
    rows = helper.select(sql)
    print(rows)


# - 年龄(age) >= 32,且显示的所有男性的年龄(age)大于女性的平均年龄(age)
def select_3():
    helper = MySqlHelper('localhost', 'root', 'ak47qbz95', 'hqyj')
    sql = '''
    select name, age from pandas_data where gender='男' and age>=32 and 
    age>(select avg(age) from pandas_data where gender='女');
    '''
    rows = helper.select(sql)
    print(rows)
# - 只显示教育长度(education)数量最多,年龄(age)>=32 的数据
def select_4():
    helper = MySqlHelper('localhost', 'root', 'ak47qbz95', 'hqyj')
    sql = '''
    select * from pandas_data where age >= 32 and education = (
    select education from pandas_data where age >= 32
    group by education order by count(*) desc limit 1
);
    '''
    rows = helper.select(sql)
    print(rows)


if __name__ == '__main__':
    create_table('dataset.csv')
    select_1()
    select_2()
    select_3()
    select_4()